CREATE VIEW [dbo].[vSoaInvoiceSummary]
AS
SELECT i.[InvoiceId],
       i.[FinancialEntityId],
       i.[InvoiceNumber],
       i.[InvoiceDate],
       i.[BillToPartyId],
       i.[SoldToPartyId],
       i.[Description],
           CASE WHEN i.[SOURCE_SYSTEM] = 'DUES' THEN 'SubscriptionBilling'
                WHEN i.[ORIGINATING_TRANS_NUM] > 0 AND EXISTS
                  (SELECT 1 FROM [dbo].[Activity] a
                      WHERE a.[ORIGINATING_TRANS_NUM] = i.[ORIGINATING_TRANS_NUM] AND a.[ACTIVITY_TYPE] = 'PLEDGE') THEN 'Pledge'
                WHEN i.[ORIGINATING_TRANS_NUM] > 0 AND EXISTS
                   (SELECT 1 FROM [dbo].[Activity] a
                      WHERE a.[ORIGINATING_TRANS_NUM] = i.[ORIGINATING_TRANS_NUM] AND a.[ACTIVITY_TYPE] = 'GIFT') THEN 'Gift'
                ELSE 'Regular'
            END
       AS InvoiceType,
       i.[InvoiceAmount],
       i.[PaymentAdjustmentAmount],
       SUM(ISNULL(pp.[Amount],0)) AS PendingPaymentAdjustmentAmount,
       i.[Balance] -SUM(ISNULL(pp.[Amount],0)) AS [Balance],
       'Accrual' AS AccountingMethod
         
  FROM [vSoaInvoiceSummaryInvoice] i
  LEFT JOIN [dbo].[vSoaPendingARPaymentAdjustment] pp ON pp.[INVOICE_REFERENCE_NUM] = i.[REFERENCE_NUM]
  WHERE i.[INVOICE_TYPE] != 'PP'
   AND ((i.[SOURCE_SYSTEM] != 'DUES'
    OR i.REFERENCE_NUM = (SELECT MAX([INVOICE_REFERENCE_NUM]) FROM [dbo].[Subscriptions] WHERE STATUS = 'A' AND [ID] = i.SoldToPartyId)))
GROUP BY i.[InvoiceId], i.[FinancialEntityId],  i.[InvoiceNumber], i.[InvoiceDate], i.[BillToPartyId], i.[SoldToPartyId],
         i.[Description], i.[InvoiceAmount], i.[PaymentAdjustmentAmount], i.[Balance], i.[ORIGINATING_TRANS_NUM], i.[SOURCE_SYSTEM]

UNION ALL

SELECT
       il.[InvoiceId] AS InvoiceId,
       (SELECT CASE WHEN (SELECT ISNULL([ShortValue],'') FROM [dbo].[System_Params]
         WHERE [ParameterName] = 'AR_Control.DefaultEntityDues')  != '' THEN (SELECT [ShortValue] FROM [dbo].[System_Params]
         WHERE [ParameterName] = 'AR_Control.DefaultEntityDues')
         ELSE (Select OrgCode from Org_Control where DefaultFlag = 1) END) AS FinancialEntityId,
       0 AS InvoiceNumber,
       MAX(il.BILL_DATE) AS InvoiceDate,
       s.BillToPartyId,
       s.SoldToPartyId,
       'Renewal Fees' AS Description,
       'SubscriptionBilling' AS InvoiceType,
       SUM(il.[ExtendedAmount])AS InvoiceAmount,
       SUM(il.[PaymentAdjustmentAmount]) AS PaymentAdjustmentAmount,
       SUM(il.[PendingPaymentAdjustmentAmount]) AS PendingPaymentAdjustmentAmount,
       SUM(il.[Balance]) AS Balance,
       'Cash' AS AccountingMethod
  FROM [dbo].[vSoaInvoiceSummaryCashBase] s INNER JOIN [dbo].[vSoaInvoiceLineCash] il ON s.[InvoiceId] = il.[InvoiceId] AND s.[SoldToPartyId] = il.[ShipToPartyId]
GROUP BY s.BillToPartyId, s.SoldToPartyId, il.[InvoiceId]


GO